from chinese_calendar import is_workday, is_holiday
import datetime
import time
import pymysql
import pandas as pd


def select_data(startDate,endDate):
    conn = pymysql.connect(host="47.100.19.231",
                                    port=3306,
                                    user="fcdb",
                                    password="fcdb",
                                    db="doquant",
                                    charset="utf8")

    cursor = conn.cursor()

    sql =  "SELECT date, open, high,low, close, volume  FROM `t_futures_transaction_minute_data` WHERE DATE>%s AND DATE<%s"
    cursor.execute(sql,(startDate,endDate))
    data = cursor.fetchall()
    # 处理查询结果，获取的数据转化为dataframe格式
    columnDes = cursor.description #获取连接对象的描述信息
    columnNames = [columnDes[i][0] for i in range(len(columnDes))] #获取列名
    df = pd.DataFrame([list(i) for i in data],columns=columnNames) #得到的data为二维元组，逐行取出，转化为列表，再转化为df
    # df.set_index('date', inplace=True)
    cursor.close()
    conn.close()
    print(df)
    # 判断当天是否有数据
    if len(df) > 0 :
        trading_day = startDate
        for item in range(10):
            # 先把当天日期转换为秒然后再减去一天
            same_day = ISOString2Time(trading_day) - 86400
            # 然后把秒转化为日期格式，用于判断是否为交易日
            before_day = Time2ISOString(same_day)
            # 将日期变量进行赋值进行
            trading_day = before_day
            # 往前推了一天判断是否为交易日
            if is_work_day(before_day):
                print('当前是交易日')
                break
        df['date'] = df['date'].map(lambda x: loadDate(x,dates = trading_day))
        print(df)
    else:
        print('当天非交易日')


def loadDate(x,dates):
    y_m_d = dates.split(' ')[0]
    h_m_s = x.split(' ')[1]
    return y_m_d + ' ' + h_m_s



def ISOString2Time( s ):
    ''' 
    convert a ISO format time to second
    from:2006-04-12 16:46:40 to:23123123
    把一个时间转化为秒
    '''
    d=datetime.datetime.strptime(s,"%Y-%m-%d %H:%M:%S")
    return time.mktime(d.timetuple())

def Time2ISOString( s ):
    ''' 
    convert second to a ISO format time
    from: 23123123 to: 2006-04-12 16:46:40
    把给定的秒转化为定义的格式
    '''
    return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime( float(s) ) )

# 判断是否为交易日
def is_work_day(s):
    d=datetime.datetime.strptime(s,"%Y-%m-%d %H:%M:%S")
    #  输出结果为bool值，True为工作日，False为休息日。
    return is_workday(d)

# 循环输出某一段日期
def for_date():
    start = '2021-09-28'
    end = '2021-11-12'
    start_hms = ' 16:00:00'
    end_hms = ' 23:59:59'
    datestart = datetime.datetime.strptime(start, '%Y-%m-%d')
    dateend = datetime.datetime.strptime(end, '%Y-%m-%d')
    while datestart < dateend:
        datestart += datetime.timedelta(days=1)
        today = datestart.strftime('%Y-%m-%d')
        select_data(today + start_hms,today + end_hms)

if __name__ == '__main__':
    for_date()
    # pass
    # select_data('2021-10-08 16:00:00','2021-10-08 23:59:00')


